package com.app.conrtoller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.app.domain.BbData;

import com.app.domain.ExternalResponse;
import com.app.domain.UploadParameter;
import com.app.domain.YjData;
import com.app.service.UploadParameterService;
import com.app.service.UploadService;
import com.app.utils.TimeHelper;
import com.app.utils.ZipUtils;
import com.csvreader.CsvReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.util.*;
import java.util.stream.Collectors;

import static java.lang.System.out;

/**
 * 报表
 */
@Slf4j
@RestController
@RequestMapping("/bb")
public class BbController {
    @Autowired
    UploadParameterService uploadParameterService;
    /**
     * 生成 Excel 文件
     * @author 云胡
     * @param multipartFile
     * @return 生成的 Excel 文件名
     * @throws IOException
     */
    /**
     * 导出excel文件
     *
     * @param multipartFile
     * @throws IOException
     */
    @RequestMapping("/upload")
    @ResponseBody
    public ExternalResponse exportExcelMb(MultipartFile multipartFile, HttpServletRequest request, HttpServletResponse response) {
        File zipFile = null;
        String outputDirPath = null;
        try {
            String excelFilePath = csvToExcelFile(multipartFile);
            File csvFile = new File(excelFilePath);
            String parent = csvFile.getParent();
            //获取excel数据
            List<BbData> bbData = readExcel(excelFilePath);
            Map<String, List<BbData>> map = bbData.stream().collect(Collectors.groupingBy(BbData::getLbh));
            outputDirPath = parent + "\\bb\\";
            File outFile = new File(outputDirPath);
            if (!outFile.exists()) {
                outFile.mkdir();
            }
            //生成报表
            writeExcel(map, outputDirPath);
            //压缩zip
            zipFile = new File(parent + "\\压浆报表.zip");
            FileOutputStream fos1 = new FileOutputStream(zipFile);
            ZipUtils.toZip(outputDirPath, fos1, false);
        } catch (IOException e) {
            log.error("error", e);
        } catch (RuntimeException e) {
            log.error("error", e);
        } finally {
            if (StringUtils.isNotEmpty(outputDirPath)) {
                try {
                    FileUtils.cleanDirectory(new File(outputDirPath));
                } catch (IOException e) {
                    log.error("error", e);
                }
            }
        }
        return new ExternalResponse(0, zipFile.getPath(), null);
    }

    @RequestMapping("/download")
    public void download(HttpServletResponse response, @RequestParam("filePath") String filePath) throws IOException {
        OutputStream outputStream = null;
        try {
            BufferedInputStream fis = new BufferedInputStream(new FileInputStream(new File(filePath)));
            String zipFileName = String.format("%s-%s.zip", "压浆报表", TimeHelper.getCurrentDate());
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(zipFileName.getBytes("UTF-8"), "ISO-8859-1"));
            // 写入response
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            outputStream = response.getOutputStream();
            outputStream.write(buffer);
        } catch (IOException e) {
            log.error("error", e);
        } catch (RuntimeException e) {
            log.error("error", e);
        } finally {
            try {
                outputStream.flush();
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void writeExcel(Map<String, List<BbData>> map, String outputDirPath) {
//        String filePath = "C:\\Users\\lenovo\\Desktop\\新建文件夹\\报表\\";
        UploadParameter uploadParameter = uploadParameterService.findOne("27");
        map.forEach((key, value) -> {

            OutputStream outputStream = null;
            try {
                Map<String, Object> maps = new HashMap<String, Object>();
                maps.put("lbh", key);
                maps.put("yjrq", value.get(0).getYjrq());
                maps.put("hjwd", value.get(0).getHjwd());
                maps.put("sgdw", uploadParameter.getSgdw());
                maps.put("gcmc", uploadParameter.getGcmc());
                maps.put("hth", uploadParameter.getHtbh());
                maps.put("jldw", uploadParameter.getJldw());
                maps.put("zh", uploadParameter.getSgbw());
                maps.put("bh", uploadParameter.getUuid());
                maps.put("qm", uploadParameter.getEquipmentname());
                maps.put("zzsj", uploadParameter.getStationed());
                maps.put("gdlx", uploadParameter.getHoleinternal());
                maps.put("lx", uploadParameter.getLblx());
                maps.put("kd", uploadParameter.getHolelength());
                maps.put("yjlsccj", uploadParameter.getSnmc());
                maps.put("yjlgg", uploadParameter.getYajiangji());
                maps.put("yjlph", uploadParameter.getKongdaoshu());
                maps.put("yjsbcj", uploadParameter.getYajiangfangxiang());
                maps.put("yjsbbh", uploadParameter.getYajiangmoshi());
                maps.put("sbccrq", uploadParameter.getZlsj());

                List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
                for (int i = 0; i < value.size(); i++) {
                    Map<String, String> lm = new HashMap<String, String>();
                    lm.put("xh", i + 1 + "");
                    lm.put("jywd", value.get(i).getJywd());
                    lm.put("ldd", value.get(i).getLdd());
                    lm.put("sjb", value.get(i).getSjb());
                    lm.put("kdbh", value.get(i).getKdbh());
                    lm.put("kssj", value.get(i).getYjrq() + " " + value.get(i).getKssj());
                    lm.put("jssj", value.get(i).getYjrq() + " " + value.get(i).getJssj());
                    lm.put("yjsj", TimeHelper.subTime(value.get(i).getYjrq() + " " + value.get(i).getKssj(), value.get(i).getYjrq() + " " + value.get(i).getJssj()).toString());
                    lm.put("byyl", value.get(i).getByyl());
                    lm.put("bysj", value.get(i).getBysj());
                    lm.put("llyjl", value.get(i).getLlyjl());
                    lm.put("sjyjl", value.get(i).getSjyjl());
                    lm.put("beiyong", "");
                    listMap.add(lm);
                }
                maps.put("maplist", listMap);
                outputStream = new FileOutputStream(new File(outputDirPath + key + "压浆记录表.xlsx"));
                TemplateExportParams params = new TemplateExportParams(
                        "export/template/bbTemplate.xlsx");
                Workbook workbook = ExcelExportUtil.exportExcel(params, maps);
                workbook.write(outputStream);
            } catch (FileNotFoundException e) {
                log.error("error", e);
            } catch (IOException e) {
                log.error("error", e);
            } finally {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });


    }

    private Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        // If clearly doesn't do mark/reset, wrap up
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }

        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }

    private List<BbData> readExcel(String csvFilePath) throws IOException {
        FileInputStream inputStream = new FileInputStream(new File(csvFilePath));
        Workbook workbook = null;
        try {
            workbook = create(inputStream);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        Sheet sheet = workbook.getSheetAt(0);

        //读取标题内容
        Row title = sheet.getRow(0);
        if (title != null) {
            int columns = title.getPhysicalNumberOfCells();//列数
            for (int cellNum = 0; cellNum < columns; cellNum++) {
                Cell cell = title.getCell(cellNum);
                if (cell != null) {
                    int cellType = cell.getCellType();//我们已知为String
                    String cellValue = cell.getStringCellValue();
                }
            }
        }

        //读取表中的内容
        int rows = sheet.getPhysicalNumberOfRows();//行数
        Map<String, List> map = new HashMap<>();
        List<BbData> bbDataList = new LinkedList<>();
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                //读取行中的列
                BbData bbData = new BbData();
                int columns = title.getPhysicalNumberOfCells();
                for (int col = 0; col < columns; col++) {
                    Cell cell = row.getCell(col);
                    //匹配列的数据类型
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、数字)
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                                    Date date = cell.getDateCellValue();
                                    if (col == 8 || col == 0) {
                                        cellValue = TimeHelper.formatDateByFormat(date, "yyyy-MM-dd");
                                    } else if (col == 9 || col == 11) {
                                        cellValue = TimeHelper.formatDateByFormat(date, "hh:mm:ss");
                                    }

                                } else {
                                    //防止数字过长,转为String
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                            case HSSFCell.CELL_TYPE_ERROR:

                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                        }

                        switch (col + 1) {
                            case 1:
                                bbData.setLbh(cellValue);
                                break;
                            case 5:
                                bbData.setKdbh(cellValue);
                                break;
                            case 8:
                                bbData.setSjyjl(cellValue);
                                break;
                            case 9:
                                bbData.setYjrq(cellValue);
                                break;
                            case 10:
                                bbData.setKssj(cellValue);
                                break;
                            case 12:
                                bbData.setJssj(cellValue);
                                break;
                            case 14:
                                bbData.setLlyjl(cellValue);
                                break;
                            case 15:
                                bbData.setSjb(cellValue);
                                break;
                            case 16:
                                bbData.setHjwd(cellValue);
                                break;
                            case 17:
                                bbData.setJywd(cellValue);
                                break;
                            case 18:
                                bbData.setBysj(cellValue);
                                break;
                            case 19:
                                bbData.setByyl(cellValue);
                                break;
                            case 21:
                                bbData.setLdd(cellValue);
                                break;
                        }
                    }

                }

                bbDataList.add(bbData);
            }
        }

        inputStream.close();
        return bbDataList;
    }

    /**
     * csv转xlsx
     *
     * @param multipartFile
     * @return
     * @throws IOException
     */
    public String csvToExcelFile(MultipartFile multipartFile) throws IOException {
        // 获取 csv 数据
        ArrayList<String[]> csvDataList = getCsvDataList(multipartFile);
        if (csvDataList.isEmpty()) {
            // 没有数据
            return "";
        }
        // 获取拥有后缀的文件名 「abc.csv」
        String csvFileNameHaveSuffix = multipartFile.getOriginalFilename();
        // 获取不带后缀的文件名 「abc」
        String csvFileName = csvFileNameHaveSuffix.substring(0, csvFileNameHaveSuffix.indexOf("."));

        // 创建一个 xlsx 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        String outputExcelFileName = csvFileName + ".xls";
        File file = new File(outputExcelFileName);
        FileOutputStream out = new FileOutputStream(file);
        outputExcelFileName = file.getAbsoluteFile().getPath();

        // 创建工作表
        XSSFSheet spreadsheet = workbook.createSheet("Sheet1");

        // 将 csv 数据存到 xlsx 文件中
        for (int rowNum = 0; rowNum < csvDataList.size(); rowNum++) {
            // 获取一行的数据
            String[] csvFileRowData = csvDataList.get(rowNum);
            XSSFRow row = spreadsheet.createRow(rowNum);
            for (int columnNum = 0; columnNum < csvFileRowData.length; columnNum++) {
                XSSFCell cell = row.createCell(columnNum);
                cell.setCellValue(csvFileRowData[columnNum]);
            }
        }

        workbook.write(out);
        out.close();
        return outputExcelFileName;
    }

    /**
     * 获取 csv 数据
     *
     * @param multipartFile
     * @throws IOException
     * @author 云胡
     */
    public ArrayList<String[]> getCsvDataList(MultipartFile multipartFile) throws IOException {
        // 存放所有的 csv 文件数据
        ArrayList<String[]> csvDataList = new ArrayList<>();

        // 文件的编码，这里设为 utf-8
        CsvReader reader = new CsvReader(multipartFile.getInputStream(), ',', Charset.forName("gbk"));

        // 获取数据
        while (reader.readRecord()) {
            csvDataList.add(reader.getValues());
        }
        // 关闭
        reader.close();
        return csvDataList;
    }

}
